#! /usr/bin/env python
# -*- coding: utf-8 -*-
# Create by Albert_Chen
# CopyRight (py) 2017年 陈超. All rights reserved by Chao.Chen.
# Create on 2017-01-19
from __future__ import absolute_import

import os
import smtplib
import mimetypes
import pandas as pd
from sqlalchemy import create_engine
from celery.utils.log import get_task_logger

from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText
from email.Encoders import encode_base64
from email.mime.base import MIMEBase

engine = create_engine('mysql+mysqlconnector://root:@localhost/CBI')
base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
logger = get_task_logger(__name__)

MAIL_HOST = 'smtp.qq.com'
EMAIL_PASSWORD = 'vbhuhpiyrqondiea'
REPLY_EMAIL = 'cbi65@qq.com'


def make_excel(yesterday, today):
    """

    :param yesterday: yesterday timestamp str
    :param today:  today timestamp str
    :return:
    """

    to_editor ='896460893@qq.com'
    to_dev = '273176752@qq.com'

    sql = """
    SELECT * FROM tenderer_recrawl WHERE datetime >= '%s' AND datetime <= '%s'
    """

    filepath = os.path.join(base_dir, 'report', yesterday + '.xlsx')
    sql = sql %(yesterday, today)
    try:
        df = pd.read_sql(sql, engine)
        df.to_excel(filepath,index=False)
    except Exception as e:

        logger.error(str(e))
        logger.info('Send email to developer: %s' % to_dev)
        send_email_to_me(yesterday=yesterday, to_list=to_dev, content=str(e))

    else:

        logger.info('Send email to editor %s' % to_editor)
        send_email_with_attachment(yesterday, to_editor, filepath)


def send_email_to_me(yesterday, to_list='273176752@qq.com', content=''):

    msg = MIMEMultipart()
    msg['From'] = REPLY_EMAIL
    msg['To'] = to_list
    msg['Subject'] = 'Error Message'
    html = """
        <html>
            <head></head>
            <body>
                <p>陈超</p>
                <p>
                    代码报错了, %s
                </p>
            </body>
        </html>
    """%(content)
    msg_html = MIMEText(html, 'html', 'utf-8')
    msg.attach(msg_html)
    server = smtplib.SMTP_SSL(MAIL_HOST)
    try:

        server.login(REPLY_EMAIL, EMAIL_PASSWORD)
        server.sendmail(REPLY_EMAIL, to_list, msg.as_string())
    except Exception as e:
        logger.error(str(e))
    else:
        logger.info('send email to %s'%to_list)
    finally:
        server.close()

def send_email_with_attachment(yesterday, to_list='', filepath=''):

    msg = MIMEMultipart()
    msg['From'] = REPLY_EMAIL
    msg['To'] = to_list
    msg['Subject'] = '天眼查%s报告' %yesterday

    ctype, encoding = mimetypes.guess_type(filepath)
    maintype, subtype = ctype.split("/", 1)
    fp = open(filepath, 'rb')
    submsg = MIMEBase(maintype, subtype)
    submsg.set_payload(fp.read())
    encode_base64(submsg)
    submsg.add_header('Content-Disposition', 'attachment', filename=yesterday + '.xlsx')
    msg.attach(submsg)
    fp.close()
    html = """
        <html>
            <head></head>
            <body>
                <p>亲爱的编辑</p>
                <p>
                    %s的天眼查报告
                </p>
            </body>
        </html>
    """%(yesterday)
    msg_html = MIMEText(html, 'html', 'utf-8')
    msg.attach(msg_html)
    server = smtplib.SMTP_SSL(MAIL_HOST)
    try:
        server.login(REPLY_EMAIL, EMAIL_PASSWORD)
        server.sendmail(REPLY_EMAIL, to_list, msg.as_string())
    except Exception as e:
        print (e)
        logger.error(str(e))
    else:
        logger.info('send email to %s' % to_list)
    finally:
        server.close()


if __name__ == "__main__":

    import argparse
    import datetime
    from dateutil.parser import parse

    parser = argparse.ArgumentParser(description="create and send a daily report to editor")
    parser.add_argument("-t", "--today", dest='today', type=str, required=True,
                        help="which day report you want to create")
    args = parser.parse_args()
    yesterday = args.today
    try:
        yesterday = parse(yesterday)
        today = yesterday + datetime.timedelta(days=1)
        yesterday_str = yesterday.strftime("%Y-%m-%d")
        today_str = today.strftime("%Y-%m-%d")
    except Exception as e:
        print("[Error] Could parse date string to date format")
        exit(1)
    else:
        print("Create %s's report" %yesterday_str)
        make_excel(yesterday_str, today_str)

    # send_email_to_me('2017-01-09', '陈超是傻逼')
    #  send_email_with_attachement('2017-01-18', os.path.join(base_dir, 'report', '2017-01-18.xlsx'))
    # (yesterday='2017-03-07', today='2017-03-08')
    # send_email_with_attachment('2017-01-19', '896460893@qq.com', os.path.join(base_dir, 'report', '2017-01-18.xlsx'))
